import os
from typing import Optional, List

from peewee import Model, CharField, IntegerField, AutoField, BooleanField, fn
from playhouse.shortcuts import model_to_dict
from pydantic import BaseModel

from db.my_sqlite import DB
from tables.faq import FAQ_DB, FAQ, FAQModel
from tables.file import File, Files


# 定义文件模型
class PreProblem(Model):
    id = AutoField(primary_key=True, help_text="预设问题id")
    userid = IntegerField(help_text="用户id")
    query = CharField(help_text="问题")
    answer = CharField(help_text="回答")
    stop = BooleanField(help_text="是否停用", default=False)
    file_id = IntegerField(help_text="文件id")
    faq_id = IntegerField(help_text="问答id")
    class Meta:
        database = DB

class FileStatisticsModel(BaseModel):
    file_id: Optional[int] = None
    filename: Optional[str] = None
    count: Optional[int] = None
    create_date: Optional[str] = None
    stop: Optional[bool] = True

class PreProblemModel(BaseModel):
    id: Optional[int]
    userid: Optional[int]
    query: Optional[str]
    answer: Optional[str]
    stop: Optional[bool] = False
    file_id: Optional[int]
    faq_id: Optional[int]

class PreProblemQueryModel(BaseModel):
    total_count: Optional[int]
    data: Optional[List[FileStatisticsModel]]

class PreProblemDetailModel(BaseModel):
    total_count: Optional[int]
    data: Optional[List[PreProblemModel]]

class PreProblemFaqModel(BaseModel):
    prologue: Optional[str]
    problems: Optional[List[PreProblemModel]]
    relative_file_path: Optional[str]
class PreProblemTable:
    def __init__(self, db):
        self.db = db
        self.db.create_tables([PreProblem])

    @staticmethod
    def delete_preset_problem_by_id(userid: int, id: int) -> bool:
        return PreProblem.delete().where(
            (PreProblem.userid == userid) &
            (PreProblem.id == id)
        ).execute() > 0

    @staticmethod
    def delete_file_preset_problem_by_id(file_id: int,userid: int) -> bool:
        return PreProblem.delete().where(
            (PreProblem.userid == userid) &
            (PreProblem.file_id == file_id)
        ).execute() > 0

    @staticmethod
    def update_preset_problem_by_id(userid: int, id: int, query: str, answer: str, stop: bool) -> bool:
        # 获取原有信息
        pres = PreProblem.select().where(PreProblem.id == id)
        tmp_list = [PreProblemModel(**model_to_dict(pre)) for pre in pres]
        old_model = tmp_list[0] if len(tmp_list) > 0 else PreProblemModel()
        # 更新信息
        return PreProblem.update(
            query=old_model.query if query is None else query,
            answer=old_model.answer if answer is None else answer,
            stop=old_model.stop if stop is None else stop,
        ).where(
            (PreProblem.userid == userid) &
            (PreProblem.id == id)
        ).execute() > 0



    @staticmethod
    def get_preset_problems(faq_id: int, query:str , limit: int, offset: int, userid: int) -> PreProblemQueryModel:

        base_query = (PreProblem
                 .select(
            PreProblem.file_id,
            File.filename,
            fn.COUNT(PreProblem.id).alias('count'),
            fn.MAX(File.create_date).alias('create_date'),
            File.stop
            )
                 .join(File, on=(PreProblem.file_id == File.id))
                 .where(
                     (PreProblem.userid == userid) &
                     (PreProblem.faq_id == faq_id)  # 添加 faq_id 条件
                 )
                 .group_by(PreProblem.file_id, File.filename,File.stop))

        if query:
            base_query = base_query.where(File.filename ** f"%{query}%")  # 添加模糊查询条件

        total_count_query = base_query.select(fn.COUNT(PreProblem.file_id))
        total_count = total_count_query.scalar()

        # 分页查询
        files = base_query.limit(limit).offset(offset)

        result = []
        for file in files:
            file_statistics = FileStatisticsModel(
                file_id=file.file_id,
                filename=file.file.filename,
                count=file.count,
                create_date=file.create_date,
                stop = file.file.stop
            )
            result.append(file_statistics)

        return PreProblemQueryModel(
            total_count=total_count,
            data=result
        )

    @staticmethod
    def get_preset_problems_detail(file_id: str, query: str,limit,offset,userid: int) -> PreProblemDetailModel:
        problems_query = PreProblem.select()
        if file_id:
            problems_query = problems_query.where(PreProblem.file_id == file_id)
        if query:
            problems_query = problems_query.where(PreProblem.query ** f"%{query}%")

        total_count_query = problems_query.select(fn.COUNT(PreProblem.id))
        total_count = total_count_query.scalar()

        problems_query = problems_query.limit(limit).offset(offset)

        result = []
        for file in problems_query:
            file_statistics = PreProblemModel(
                id=file.id,
                userid=file.userid,
                query=file.query,
                answer=file.answer,
                stop=file.stop,
                file_id = file.file_id,
                faq_id = file.faq_id
            )
            result.append(file_statistics)

        return PreProblemDetailModel(
            total_count=total_count,
            data=result
        )
    @staticmethod
    def get_preset_problems_by_faq(faq_id: str,limit:int ,offset:0):
        problems_query = PreProblem.select()
        if faq_id:
            problems_query = problems_query.where(PreProblem.faq_id == faq_id)

        problems_query = problems_query.limit(limit).offset(offset)
        prologue = ""
        relative_path=""
        # faq_query = FAQ_DB.select()
        if faq_id:
            # faq_query = faq_query.(FAQ.id == faq_id)

            faq_res = FAQ.get_or_none(FAQ.id == faq_id)
            if faq_res:
                faq_model = FAQModel(**model_to_dict(faq_res))
                prologue = faq_model.prologue
                file_res = Files.get_file_by_id(faq_model.file_id)
                if file_res:
                    marker = 'user_files'
                    # 获取绝对路径
                    absolute_path = os.path.abspath(file_res.filepath)
                    # 找到 marker 在路径中的位置
                    marker_index = absolute_path.find(marker)
                    # 提取 marker 之后的部分
                    if marker_index != -1:
                        relative_path = absolute_path[marker_index + len(marker):]

        result = []
        for file in problems_query:
            file_statistics = PreProblemModel(
                id=file.id,
                userid=file.userid,
                query=file.query,
                answer=file.answer,
                stop=file.stop,
                file_id=file.file_id,
                faq_id=file.faq_id
            )
            result.append(file_statistics)

        return PreProblemFaqModel(
            prologue=prologue,
            relative_file_path=relative_path,
            problems=result
        )

    @staticmethod
    def get_preset_problems_by_fileid(file_id: str) -> List[
        PreProblemModel]:
        problems_query = PreProblem.select()
        if file_id:
            problems_query = problems_query.where(PreProblem.file_id == file_id)

        result = []
        for file in problems_query:
            file_statistics = PreProblemModel(
                id=file.id,
                userid=file.userid,
                query=file.query,
                answer=file.answer,
                stop=file.stop,
                file_id=file.file_id,
                faq_id=file.faq_id
            )
            result.append(file_statistics)
        return result
    @staticmethod
    def create_preset_problem(userid: int, query: str, answer: str, file_id: int,faq_id:int) -> Optional[PreProblemModel]:
        file = PreProblem.create(
            userid=userid,
            query=query,
            answer=answer,
            file_id=file_id,
            faq_id = faq_id
        )
        return PreProblemModel(**model_to_dict(file))


PreProblems = PreProblemTable(DB)
